import tushare as ts
from sqlalchemy import create_engine
import pandas as pd
import logging
from ListUtils import anti_join
"""
上市公司数据
"""


def insert_history_data_stock_company(engine, pro):
    """
    保存历史数据，通过对比添加数据
    """
    # print(pro.stock_company(exchange='SZSE').loc[1])
    insert_history_data_flag = True
    if insert_history_data_flag:
        # ts数据
        df_stock_company_sh = pro.stock_company(exchange='SSE') # SSE上交所 SZSE深交所
        df_stock_company_sz = pro.stock_company(exchange='SZSE')
        df_stock_company = pd.concat((df_stock_company_sh,df_stock_company_sz),axis=0)
        df_stock_company = df_stock_company.reset_index(drop=True)
        # 数据库数据
        sql = 'select s.ts_code from stock_company s'
        target_list = pd.read_sql(sql, engine)
        # print(df_stock_company.loc[0])
        if len(target_list) == 0:
            # 数据库内未找到数据则全部添加
            df_stock_company.to_sql('stock_company', con=engine, index=False, index_label='trade_code', if_exists='append')
            logging.debug('数据库内未找到数据则全部添加：(%s)' % df_stock_company)
        else:
            # 数据库内有历史数据则增量添加
            df_stock_company = anti_join(df_stock_company, target_list, 'ts_code')
            # 采用双for循环性能太差，采用dataFrame合并提高效率
            # for i in range(0, len(df_stock_company)):
            #     # 查询并插入
            #     that_ = df_stock_company.loc[i].ts_code
            #
            #     for j in range(0, len(target_list)):
            #
            #         # logging.debug('下方if判断：' + trade_cal_days.loc[j].cal_date == that_day and trade_cal_days.loc[j].is_open == '1')
            #         if not target_list.loc[j].exists and target_list.loc[j].ts_code == that_:
            #             print(j)
            #             # print(source_list.loc[i,['exists']])
            #             df_stock_company.loc[i,['exists']] = True
            #             target_list.loc[j, ['exists']] = True
            #             break

            if len(df_stock_company) > 0:
                logging.debug('增量：(%s)' % df_stock_company)
                # print(source_list)
                # df_stock_company.drop(labels='exists',axis=1,inplace=True)
                df_stock_company.to_sql('stock_company', con=engine, index=False, index_label='trade_code', if_exists='append')
        # logging.debug('stock_company接口返回数据：(%s)' %df_stock_company)

        # 更新覆盖原有数据(试验得将会导致数据表信息改变，不推荐)
        # df_stock_company.to_sql(name='stock_company', con=engine, index=False, index_label='trade_code', if_exists='replace')



def link_stock_company(engine):
    """
    联表查询基本信息
    """
    test_flag = False
    if test_flag:
        sql = 'select s.*, sc.exchange, sc.chairman, sc.manager, sc.secretary, sc.reg_capital, sc.setup_date, sc.province, sc.city, sc.introduction, sc.website, sc.email, sc.office, sc.employees, sc.main_business, sc.business_scope from stock s LEFT JOIN stock_company sc ON s.ts_code = sc.ts_code'
        target_list = pd.read_sql(sql, engine)
        logging.debug('股票基本数据：(%s)' %target_list)
        logging.debug(' end of program ')